//
//  NGDBHelper.m
//  NetMobileLocation
//
//  Created by Mehmet Sinan Şahin on 1/22/12.
//  Copyright (c) 2012 netgis. All rights reserved.
//

#import "NGDBHelper.h"
#import "NGLocation.h"
#import "NGDeviceData.h"
#import "NSDate+Extensions.h"
#import "NSString+Extensions.h"
#import "/usr/include/sqlite3.h"
#import "SetLocationsResponse.h"
#import "SharedPrefs.h"
#import "NGUtility.h"


#define _DB_MODEL_VERSION   1

@interface NGDBHelper()
- (NSMutableArray *) _listMessages:(MESSAGE_TYPE)type;
- (BOOL) _insertLocation:(NGLocation *)location logType: (NGLOC_LOG_TYPE) logType;
- (BOOL) _deleteLocations: (NSString *)whereSql; //if whereSql is nil, deletes all locations

@end

@implementation NGDBHelper

NSString * const TABLE_NAME_LOCATIONS = @"Locations";
NSString * const TABLE_NAME_MESSAGES = @"Messages";

static NGDBHelper *db_;
static sqlite3 *ngDB;

@synthesize databasePath;

+ (NGDBHelper*)openDatabase
{
	const int db_version = [SharedPrefs getDBModelVersion];
	if (_DB_MODEL_VERSION != db_version) {
		NSLog(@"Preious DB model %d, I was expecting %d. Purging!", db_version,
             _DB_MODEL_VERSION);
		[NGDBHelper purge];
	}
    
    if (!db_) {
        db_ = [NGDBHelper new];
        db_.databasePath = [NGDBHelper path];
        
        NSFileManager *filemgr = [NSFileManager defaultManager];
        
        //if database file does not exists, create
        NSLog(@"DB file path:%@", db_.databasePath);
        if ([filemgr fileExistsAtPath: db_.databasePath ] == NO)
        {	
            const char *dbpath = [db_.databasePath UTF8String];
            
            if (sqlite3_open(dbpath, &ngDB) == SQLITE_OK) {
                char *errMsg;
                const char *createSQLLocations = "CREATE TABLE IF NOT EXISTS Locations (ID INTEGER PRIMARY KEY AUTOINCREMENT, Lat TEXT, Lon TEXT, Time TEXT, Status INTEGER, Speed TEXT, SENT INTEGER DEFAULT 0)";
                
                if (sqlite3_exec(ngDB, createSQLLocations, NULL, NULL, &errMsg) != SQLITE_OK) {
                    NSLog(@"Failed to create table");
                }
                
                const char *createSQLMessages = "CREATE TABLE IF NOT EXISTS Messages (ID INTEGER PRIMARY KEY AUTOINCREMENT, Sender TEXT, Time TEXT, Command TEXT, CommandParameter TEXT, Selected INTEGER DEFAULT 0, Read INTEGER DEFAULT 0)";
                
                if (sqlite3_exec(ngDB, createSQLMessages, NULL, NULL, &errMsg) != SQLITE_OK) {
                    NSLog(@"Failed to create table");
                }

                sqlite3_close(ngDB);
                
            } else {
                NSLog(@"Failed to open/create database");
            }
        }        
    }
    
    return db_;
}

/** Tries to remove the database file.
 * You should close the database before trying to remove it.
 *
 * Returns YES if the file was deleted.
 */
+ (BOOL)purge
{
	NSString *path = [NGDBHelper path];
	NSFileManager *manager = [NSFileManager defaultManager];
    
	if (![manager fileExistsAtPath:path])
		return NO;
    
	NSError *error = nil;
	if ([manager removeItemAtPath:path error:&error]) {
		NSLog(@"Deleted %@", path);
		return YES;
	} else {
		NSLog(@"Couldn't unlink %@: %@", path, error);
		return NO;
	}
}


/** Returns the path to the database filename.
 */
+ (NSString*)path
{
    NSString *docsDir;
    NSArray *dirPaths;
    
    // Get the documents directory
    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    
    docsDir = [dirPaths objectAtIndex:0];
    
    // Build the path to the database file
    return [[[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: @"NG_DB.db"]] autorelease];
}


#pragma mark - LOCATION methods 

- (BOOL) existsLocation:(NGLocation *)location  logType: (NGLOC_LOG_TYPE) logType{
    
    BOOL result = FALSE;
    sqlite3_stmt    *statement;
    
    if (sqlite3_open([databasePath UTF8String], &ngDB) == SQLITE_OK)
    {
         NSString *querySQL = 
            [NSString stringWithFormat: @"SELECT ID FROM %@ WHERE Time = '%@'AND SENT = %d",
             TABLE_NAME_LOCATIONS, 
             [location.tarihSaat toFormattedString],
             logType];
        
        if (sqlite3_prepare_v2(ngDB, [querySQL UTF8String], -1, &statement, NULL) == SQLITE_OK)
        {
            if (sqlite3_step(statement) == SQLITE_ROW)
            {
                result = TRUE;
            } 
            sqlite3_finalize(statement);
        }
        sqlite3_close(ngDB);
    }    
    
    return result;
}

/** sunucuya gonderilmedigi zaman burasi calisacak */
- (BOOL) _insertLocation:(NGLocation *)location logType:(NGLOC_LOG_TYPE)logType {

    if ([self existsLocation:location logType:logType]) {
        return FALSE;
    }
    
    //Control maxLocation size exceedence
    NSString *deleteStatement = nil;
    int locationSize = [self getRecordSize:TABLE_NAME_LOCATIONS whereSql: [NSString stringWithFormat:@"SENT = %d", logType]];
    int maxLocationSize = 0;
    
    if (logType == NGLOC_UNSENT) {
        maxLocationSize = [SharedPrefs getLocationSize];
    } else if (logType == NGLOC_FORLOG){
        maxLocationSize = MAX_VALUES_TO_BE_DISPLAYED;        
    }
    
    
    if (maxLocationSize <= locationSize) {
        //delete oldest location
        NSMutableArray *oldestIds = [self getOldestId:TABLE_NAME_LOCATIONS size:1];
        int oldestId = [((NSNumber *)[oldestIds objectAtIndex:0]) intValue];
        deleteStatement = [NSString stringWithFormat:@"DELETE FROM %@ WHERE ID = %d", 
                                     TABLE_NAME_LOCATIONS,
                                     oldestId
                                     ];        
    }
    
    BOOL result = TRUE;
    if (sqlite3_open([databasePath UTF8String], &ngDB) == SQLITE_OK)
    {
        if (deleteStatement != nil) { //delete oldest record
            char *errorDelete;
            sqlite3_exec(ngDB, [deleteStatement UTF8String], NULL, NULL, &errorDelete);
            if (errorDelete != nil) {
                NSLog(@"%s", errorDelete);
                return FALSE;
            }
        }
        NSString *insertStatement = [NSString stringWithFormat:@"INSERT INTO Locations(Lat, Lon, Time, Status, Speed, SENT) VALUES ('%f', '%f', '%@', %d, '%@', %d)", 
                                     location.lat,
                                     location.lon,
                                     [location.tarihSaat toFormattedString],
                                     location.status,
                                     location.speed,
                                     logType
                                     ];
        
        char *error;
        sqlite3_exec(ngDB, [insertStatement UTF8String], NULL, NULL, &error);
        sqlite3_close(ngDB);
        
        if (error != nil) {
            NSLog(@"%s", error);
            result = FALSE;
        }
    } else {
        NSLog(@"Failed to open/create database");
        result = FALSE;
    }
    return result;
}

- (BOOL) insertLocationForLog:(NGLocation *)location {
    NSLog(@"Insert Location For Log %@", location);
    return [self _insertLocation:location logType:NGLOC_FORLOG];
}

- (BOOL) insertLocationForUnsent:(NGLocation *)location {
    NSLog(@"Insert Location For Unsent %@", location);
    return [self _insertLocation:location logType:NGLOC_UNSENT];    
}

- (BOOL) _deleteLocations: (NSString *)whereSql {
    if (whereSql == nil) {
        whereSql = @"1 = 1";
    }
    BOOL result = TRUE;
    if (sqlite3_open([databasePath UTF8String], &ngDB) == SQLITE_OK)
    {
        NSString *deleteStatement = [NSString stringWithFormat:@"DELETE FROM %@ WHERE %@", TABLE_NAME_LOCATIONS, whereSql];
        
        char *error;
        sqlite3_exec(ngDB, [deleteStatement UTF8String], NULL, NULL, &error);
        sqlite3_close(ngDB);
        
        if (error != nil) {
            NSLog(@"%s", error);
            result = FALSE;
        }
    } else {
        NSLog(@"Failed to open/create database");
        result = FALSE;
    }
    return result;
}

- (BOOL) deleteLocations: (NGLOC_LOG_TYPE) logType {
    return [self _deleteLocations:[NSString stringWithFormat:@"SENT = %d", logType]];
}

- (BOOL) deleteAllLocations {
    return [self _deleteLocations:nil];
}

- (BOOL) sendLocation:(int)_id {
    BOOL result = TRUE;
    if (sqlite3_open([databasePath UTF8String], &ngDB) == SQLITE_OK)
    {
        
        NSString *deleteStatement = [NSString stringWithFormat:@"DELETE FROM %@ WHERE ID = %d", 
                                     TABLE_NAME_LOCATIONS,
                                     _id
                                     ];
        
        char *error;
        sqlite3_exec(ngDB, [deleteStatement UTF8String], NULL, NULL, &error);
        sqlite3_close(ngDB);
        
        if (error != nil) {
            NSLog(@"%s", error);
            result = FALSE;
        }
    } else {
        NSLog(@"Failed to open/create database");
        result = FALSE;
    }
    return result;
}

/*
 * locations array consists fo NGLocation objects
 */
- (BOOL) sendLocations: (NSMutableArray *)locations {
    if (locations == nil || [locations count] <= 0) {
        return  TRUE;
    }
    BOOL result = TRUE;
    if (sqlite3_open([databasePath UTF8String], &ngDB) == SQLITE_OK)
    {
        NSString *idsStr = @"";
        for (NGDeviceData *location in locations) {
            idsStr = [NSString stringWithFormat:@"%d,%@", location._id, idsStr];
        }
        
        idsStr = [NSString stringWithFormat:@"%@-99", idsStr];
        NSString *deleteStatement = 
        [NSString stringWithFormat:@"DELETE FROM %@ WHERE ID in (%@)",
         TABLE_NAME_LOCATIONS, idsStr];
        
        char *error;
        sqlite3_exec(ngDB, [deleteStatement UTF8String], NULL, NULL, &error);
        sqlite3_close(ngDB);
        
        if (error != nil) {
            NSLog(@"%s", error);
            result = FALSE;
        }
    } else {
        NSLog(@"Failed to open/create database");
        result = FALSE;
    }
    return result;
}

/*
 * auto released array is returned
 */
- (NSMutableArray *)listLocations:(NSString *)whereSql {
    
    if (whereSql == nil) {
        whereSql = @"1 = 1";
    }
    
    NSMutableArray *list = [[NSMutableArray alloc] init];
    sqlite3_stmt    *statement;
    
    if (sqlite3_open([databasePath UTF8String], &ngDB) == SQLITE_OK)
    {
        NSString *querySQL = [NSString stringWithFormat: @"SELECT * FROM %@ WHERE %@ ORDER BY Time DESC", TABLE_NAME_LOCATIONS, whereSql];
        
        if (sqlite3_prepare_v2(ngDB, [querySQL UTF8String], -1, &statement, NULL) == SQLITE_OK)
        {
            int index = 0;
            while (sqlite3_step(statement) == SQLITE_ROW)
            {
                int _id =  sqlite3_column_int(statement, 0);
                NSString *lat = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)];
                NSString *lon = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 2)];
                NSString *tarihSaat = [[[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 3)] autorelease];
                int status =  sqlite3_column_int(statement, 4);
                NSString *speed = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 5)];
                
                int sent = sqlite3_column_int(statement, 6);
                
                //NGLocation *location = [[NGLocation alloc] autorelease];
                NGLocation *location = [[NGLocation alloc] init];

                location.lat = [lat doubleValue];
                location.lon = [lon doubleValue];
                location.tarihSaat = [tarihSaat toDate];
                location.status = status;
                location.speed = speed;
                location.sent = (sent == 1 ? TRUE : FALSE);
                location._id = _id; //for afterwards deletion
                
                [list insertObject:location atIndex:index++];
                
                [lon release];
                [lat release];
                [speed release];
            } 
            sqlite3_finalize(statement);
        }
        sqlite3_close(ngDB);
    }    
    
    return list;    
}


- (NSMutableArray *)listUnsentLocations {
    return [self listLocations:[NSString stringWithFormat:@"SENT = %d", NGLOC_UNSENT]];
}

- (NSMutableArray *)listLoggedLocations {
    return [self listLocations:[NSString stringWithFormat:@"SENT = %d", NGLOC_FORLOG]];
}

- (long) howManyLocations: (NGLOC_LOG_TYPE) logType {
    return [self getRecordSize:TABLE_NAME_LOCATIONS 
                      whereSql:[NSString stringWithFormat:@"SENT = %d", NGLOC_FORLOG]];
}

#pragma mark - MESSAGES methods

- (BOOL) insertMessage:(Message *)message {
    NSString *deleteStatement = nil;
    int locationSize = [self howManyMessages];
    if (MESSAGES_SIZE <= locationSize) {
        //delete oldest location
        NSMutableArray *oldestIds = [self getOldestId:TABLE_NAME_MESSAGES size:1];
        int oldestId = [((NSNumber *)[oldestIds objectAtIndex:0]) intValue];
        deleteStatement = [NSString stringWithFormat:@"DELETE FROM %@ WHERE ID = %d", 
                           TABLE_NAME_MESSAGES,
                           oldestId
                           ];
    }
    
    BOOL result = TRUE;
    if (sqlite3_open([databasePath UTF8String], &ngDB) == SQLITE_OK)
    {
        if (deleteStatement != nil) { //delete oldest record
            char *errorDelete;
            sqlite3_exec(ngDB, [deleteStatement UTF8String], NULL, NULL, &errorDelete);
            if (errorDelete != nil) {
                NSLog(@"%s", errorDelete);
                return FALSE;
            }
        }
        
        NSString *insertStatement = [NSString stringWithFormat:@"INSERT INTO Messages(Sender, Time, Command, CommandParameter, Selected) VALUES ('%@', '%@', '%@', '%@', 0)", 
                                     message.Sender,
                                     message.Time,
                                     //message.Type,
                                     //message.MessageHeader,
                                     //message.MessageBody,
                                     message.Command,
                                     message.CommandParameter
                                     ];
        
        char *error;
        sqlite3_exec(ngDB, [insertStatement UTF8String], NULL, NULL, &error);
        sqlite3_close(ngDB);
        
        if (error != nil) {
            NSLog(@"%s", error);
            result = FALSE;
        }
    } else {
        NSLog(@"Failed to open/create database");
        result = FALSE;
    }
    return result;
}

- (BOOL) insertMessages:(NSMutableArray *)messages {
    if (messages == nil || [messages count] < 1) {
        return FALSE;
    }
    NSString *deleteStatement = nil;
    int locationSize = [self howManyMessages] + [messages count];
    if (MESSAGES_SIZE <= locationSize) {
        //delete oldest locations
        int size = locationSize - MESSAGES_SIZE;
        NSMutableArray *oldestIds = [self getOldestId:TABLE_NAME_MESSAGES size:size];
        NSString *oldestIdsql = @"";
        for (NSNumber *num in oldestIds) {
            oldestIdsql = [NSString stringWithFormat:@"%d,%@", [num intValue], oldestIdsql];
        }
        oldestIdsql = [NSString stringWithFormat:@"%@-99", oldestIdsql];
        
        deleteStatement = [NSString stringWithFormat:@"DELETE FROM %@ WHERE ID in (%@)", 
                           TABLE_NAME_MESSAGES,
                           oldestIdsql
                           ];
    }    
    
    BOOL result = TRUE;
    if (sqlite3_open([databasePath UTF8String], &ngDB) == SQLITE_OK)
    {
        if (deleteStatement != nil) { //delete oldest records
            char *errorDelete;
            sqlite3_exec(ngDB, [deleteStatement UTF8String], NULL, NULL, &errorDelete);
            if (errorDelete != nil) {
                NSLog(@"%s", errorDelete);
                return FALSE;
            }
        }
        
        for (Message *message in messages) {
            NSString *insertStatement = [NSString stringWithFormat:@"INSERT INTO Messages(Sender, Time, Command, CommandParameter, Selected) VALUES ('%@', '%@', '%@', '%@', 0)", 
                                         message.Sender,
                                         message.Time,
                                         //message.Type,
                                         //message.MessageHeader,
                                         //message.MessageBody,
                                         message.Command,
                                         message.CommandParameter
                                         ];
            
            char *error;
            sqlite3_exec(ngDB, [insertStatement UTF8String], NULL, NULL, &error);            
            if (error != nil) {
                NSLog(@"%s", error);
                result = FALSE;
                break;
            }
        }
        sqlite3_close(ngDB);        
    } else {
        NSLog(@"Failed to open/create database");
        result = FALSE;
    }
    return result;
}

- (NSMutableArray *)listMessages:(MESSAGE_TYPE) type {
    return [self _listMessages:type];    
}

/*private*/
- (NSMutableArray *)listAllMessages {
    return [self _listMessages:ALL];
}

-(NSMutableArray *) _listMessages:(MESSAGE_TYPE) type {
    
    NSMutableArray *list = [[NSMutableArray alloc] init];
    sqlite3_stmt    *statement;
    
    if (sqlite3_open([databasePath UTF8String], &ngDB) == SQLITE_OK)
    {
        NSString *commandWhere = @"1=1";
        if (type == COMMAND) {
            commandWhere = @"Command > '0'";
        } else if (type == MESSAGE) {
            commandWhere = @"Command = '0'";
        }
        
        NSString *querySQL = [NSString stringWithFormat: @"SELECT * FROM %@ WHERE %@ ", TABLE_NAME_MESSAGES, commandWhere];
        [commandWhere release];
        if (sqlite3_prepare_v2(ngDB, [querySQL UTF8String], -1, &statement, NULL) == SQLITE_OK)
        {
            int index = 0;
            while (sqlite3_step(statement) == SQLITE_ROW)
            {
                int _id =  sqlite3_column_int(statement, 0);
                NSString *sender = [[[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)] autorelease];
                NSString *time = [[[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 2)] autorelease];
                const char *comm = (char *)sqlite3_column_text(statement, 3);

                NSString *command = @"";
                if (comm != nil) {
                    command = [[NSString alloc] initWithUTF8String:comm];
                }
                NSString *commandParameter = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 4)];
                
                int read =  sqlite3_column_int(statement, 6);
                                
                Message *message = [[[Message alloc] init] autorelease];
                message.Sender = sender;
                message.Time = time;
                message.Command = command;
                message.CommandParameter = commandParameter;
                message._id = _id; //for afterwards deletion
                [message setReadStatus:read];
                
                [list insertObject:message atIndex:index++];
                
                [command release];
                [commandParameter release];
            } 
            sqlite3_finalize(statement);
        }
        sqlite3_close(ngDB);
    }    
    
    return list;    
}

- (BOOL) deleteAllMessages {
    BOOL result = TRUE;
    if (sqlite3_open([databasePath UTF8String], &ngDB) == SQLITE_OK)
    {
        
        NSString *deleteStatement = [NSString stringWithFormat:@"DELETE FROM %@", 
                                     TABLE_NAME_MESSAGES];
        
        char *error;
        sqlite3_exec(ngDB, [deleteStatement UTF8String], NULL, NULL, &error);
        sqlite3_close(ngDB);
        
        if (error != nil) {
            NSLog(@"%s", error);
            result = FALSE;
        }
    } else {
        NSLog(@"Failed to open/create database");
        result = FALSE;
    }
    return result;    
}

//TODO, multi message deletion
- (BOOL) deleteMessage: (int []) ids {
    return TRUE;
}

- (BOOL) markMessageAsRead: (int)_id {
    BOOL result = TRUE;
    if (sqlite3_open([databasePath UTF8String], &ngDB) == SQLITE_OK)
    {
        NSString *updateStatement = [NSString stringWithFormat:@"UPDATE %@ SET Read = 1 WHERE ID = %d", 
                                     TABLE_NAME_MESSAGES,
                                     _id];
        
        char *error;
        sqlite3_exec(ngDB, [updateStatement UTF8String], NULL, NULL, &error);
        sqlite3_close(ngDB);
        
        if (error != nil) {
            NSLog(@"%s", error);
            result = FALSE;
        }
    } else {
        NSLog(@"Failed to open/create database");
        result = FALSE;
    }
    return result;
}

- (BOOL) deleteMessageWithId: (int) _id {
    BOOL result = TRUE;
    if (sqlite3_open([databasePath UTF8String], &ngDB) == SQLITE_OK)
    {
        NSString *deleteStatement = [NSString stringWithFormat:@"DELETE FROM %@ where ID = %d", 
                                     TABLE_NAME_MESSAGES, _id];
        
        char *error;
        sqlite3_exec(ngDB, [deleteStatement UTF8String], NULL, NULL, &error);
        sqlite3_close(ngDB);
        
        if (error != nil) {
            NSLog(@"%s", error);
            result = FALSE;
        }
    } else {
        NSLog(@"Failed to open/create database");
        result = FALSE;
    }
    return result;      
}

- (long) howManyMessages {
    return [self getRecordSize:TABLE_NAME_MESSAGES whereSql:nil];
}

- (long) howManyNewMessages {
    return [self getRecordSize:TABLE_NAME_MESSAGES whereSql:@" Read = 0 "];
}

#pragma mark - General methods

- (long) getRecordSize:(NSString *)tableName whereSql:(NSString *)whereSql {
    if (whereSql == nil) {
        whereSql = @"1 = 1";
    }
    int count = 0;
    if (sqlite3_open([databasePath UTF8String], &ngDB) == SQLITE_OK)
    {
        NSString *deleteStatement = [NSString stringWithFormat:@"SELECT COUNT(*) FROM %@ WHERE %@", tableName, whereSql];
        sqlite3_stmt *statement;
        
        if( sqlite3_prepare_v2(ngDB, [deleteStatement UTF8String], -1, &statement, NULL) == SQLITE_OK )
        {
            //Loop through all the returned rows (should be just one)
            while( sqlite3_step(statement) == SQLITE_ROW )
            {
                count = sqlite3_column_int(statement, 0);
            }
        }
        else
        {
            NSLog( @"Failed from sqlite3_prepare_v2. Error is:  %s", sqlite3_errmsg(ngDB) );
        }
        
        // Finalize and close database.
        sqlite3_finalize(statement);
        sqlite3_close(ngDB);
    }
    
    return count;    
}

- (NSMutableArray *) getOldestId:(NSString *)tableName size:(int)size {
    NSMutableArray *minId = [[[NSMutableArray alloc] initWithCapacity:size] autorelease];
    
    if (sqlite3_open([databasePath UTF8String], &ngDB) == SQLITE_OK)
    {
        NSString *deleteStatement = [NSString stringWithFormat:@"SELECT ID FROM %@ order by ID ASC limit %d", 
                                     tableName, size];
        sqlite3_stmt *statement;
        
        if( sqlite3_prepare_v2(ngDB, [deleteStatement UTF8String], -1, &statement, NULL) == SQLITE_OK )
        {
            while( sqlite3_step(statement) == SQLITE_ROW )
            {
                [minId addObject: [NSNumber numberWithInt:sqlite3_column_int(statement, 0)]];
            }
        }
        else
        {
            NSLog( @"Failed from sqlite3_prepare_v2. Error is:  %s", sqlite3_errmsg(ngDB) );
        }
        
        // Finalize and close database.
        sqlite3_finalize(statement);
        sqlite3_close(ngDB);
    }
    
    return minId;    
}


-(void) dealloc {
    [databasePath release];
    [super dealloc];
}

@end
